﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;
using System.Data.OleDb;
using Maticsoft.DBUtility;
using System.Xml;

namespace Maticsoft.Web.CERTI
{
    public partial class import : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                HiddenField1.Value = Server.MapPath("/");
                if (Session["u"] == null)
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "", "if(top){top.location.href='../Login.aspx'}else{location.href='../Login.aspx'};", true);
                }

                try
                {
                    //Button1_Click("com", "");
                    //Response.Cookies["comp"].Expires = DateTime.Now.AddDays(-1);
                }
                catch (Exception)
                {

                    //throw;
                }

            }
            else
            {
                int a = 0;
            }

            object aa = Request.Form["Name"];
            if (aa != null)
            {
                if (aa.ToString().Equals("1"))
                {
                    //DataTable dt = ViewState["table"] as DataTable;
                    //Response.Write(dt.Rows.Count);
                    //Response.End();
                }
                else if (aa.ToString().Equals("keyu"))
                {
                    //for (int i = 0; i < 10000000; i++)
                    //{
                    //    Response.Clear();
                    //    Response.Write(i);
                    //    //Thread.Sleep(100);
                    //    Response.End();
                    //}

                    Response.Clear();
                    Response.Write("comp");
                    //Thread.Sleep(100);
                    Response.End();

                    //Response.End();    
                }

            }

        }

        protected void btnSave_Click(object sender, EventArgs e)
        {

            if (FileUpload1.HasFile)
            {
                if (Path.GetExtension(FileUpload1.FileName).Trim().ToLower().Equals(".xls") || Path.GetExtension(FileUpload1.FileName).Trim().ToLower().Equals(".xlsx"))
                {

                    try
                    {
                        string filename = DateTime.Now.ToString("yyyyMMddHHmmssssfff") + Path.GetExtension(FileUpload1.FileName).Trim().ToLower();
                        string pat = Server.MapPath("~/Excel/");
                        FileUpload1.SaveAs(pat + filename);
                        //DataTable dt = GetExcelDataTable(FileUpload1.PostedFile.FileName, "Sheet1");
                        DataTable dt = GetExcelDataTable(pat + filename, "Sheet1");

                        dt = (ClearNullData(dt));
                        //DataView dv=dt.DefaultView;


                        int res = insertdatas(dt);

                        Label1.Text = "Excel中共包含有效数据 " + dt.Rows.Count + " 条,成功导入：" + res + " 条数据&nbsp;&nbsp;&nbsp;&nbsp;未导入：" + (dt.Rows.Count - res) + "条数据&nbsp;&nbsp;&nbsp;&nbsp;其中 " + exist + " 条数据已存在";
                    }
                    catch (Exception ex)
                    {

                        Label1.Text = "导入失败，错误信息如下： <br /><span style='color:red'>" + ex.Message + "<br />" + ex.Source + "</span>";
                    }

                }
                else
                {
                    Maticsoft.Common.MessageBox.ShowAndRedirect(this, "请选择正确的 Excel 文件！", "Add.aspx");

                }

            }
            else
            {
                Maticsoft.Common.MessageBox.ShowAndRedirect(this, "请选择文件！", "Add.aspx");

            }
            //Maticsoft.Common.MessageBox.ShowAndRedirect(this, "保存成功！", "list.aspx");

        }


        public void btnCancle_Click(object sender, EventArgs e)
        {
            Response.Redirect("list.aspx");
        }

        public DataTable ClearNullData(DataTable dt)
        {
            int x = dt.Rows.Count;
            DataTable da = new DataTable();
            int y = 0;
            for (int i = 0; i < x; i++)
            {
                if (dt.Rows[i][0] == null)
                {
                    dt.Rows.RemoveAt(i);
                }
                if (dt.Rows[i][0] != null && dt.Rows[i][0].ToString().Trim().Trim().Trim().Length < 2)
                {
                    //dt.Rows.RemoveAt(i);
                    dt.Rows[i].Delete();
                    //dt.Rows.Remove(dt.Rows[i]);

                    //da.Rows.Add(dt.Rows[i]);
                    //da.ImportRow(dt.Rows[i]);

                    y++;
                }
            }

            //DataTable daa = dt.Clone();
            //foreach (DataRow dr in dt.Rows)
            //{
            //    if (dr != null && dr[0].ToString().Trim().Trim().Length > 2)
            //    {
            //        //dt.Rows.RemoveAt(i);
            //        //da.Rows.Add(dt.Rows[i]);
            //        //da.ImportRow(dr);
            //        //dt.Rows.Remove(dr);

            //        DataRow r = da.NewRow();
            //        r = dr;
            //        da.Rows.Add(r);
            //        //y++;
            //    }

            //}
            dt.AcceptChanges();
            return dt;
        }

        public DataTable GetExcelDataTable(string path, string tname)
        {
            /*Office 2007*/
            string ace = "Microsoft.ACE.OLEDB.12.0";
            /*Office 97 - 2003*/
            string jet = "Microsoft.Jet.OLEDB.4.0";
            string xl2007 = "Excel 12.0 Xml";
            string xl2003 = "Excel 8.0";
            string imex = "IMEX=1";
            /* csv */
            string text = "text";
            string fmt = "FMT=Delimited";
            string hdr = "Yes";
            string conn = "Provider={0};Data Source={1};Extended Properties=\"{2};HDR={3};{4}\";";
            string select = string.Format("SELECT * FROM [{0}$]", tname);
            //string select = sql;
            string ext = Path.GetExtension(path);
            OleDbDataAdapter oda;
            DataTable dt = new DataTable("data");
            switch (ext.ToLower())
            {
                case ".xlsx":
                    conn = String.Format(conn, ace, Path.GetFullPath(path), xl2007, hdr, imex);
                    break;
                case ".xls":
                    conn = String.Format(conn, jet, Path.GetFullPath(path), xl2003, hdr, imex);
                    break;
                case ".csv":
                    conn = String.Format(conn, jet, Path.GetDirectoryName(path), text, hdr, fmt);
                    //sheet = Path.GetFileName(path);
                    break;
                default:
                    break;
                //throw new Exception("File Not Supported!");
            }
            OleDbConnection con = new OleDbConnection(conn);
            con.Open();
            //select = string.Format(select, sql);
            oda = new OleDbDataAdapter(select, con);
            oda.Fill(dt);
            con.Close();
            return dt;
        }



        private void Button1_Click(string key, string value)
        {
            string filename = Server.MapPath("/") + @"\W.xml";
            XmlDocument xmldoc = new XmlDocument();
            xmldoc.Load(filename);

            XmlNodeList topM = xmldoc.DocumentElement.ChildNodes;
            foreach (XmlElement element in topM)
            {
                if (element.Name.ToLower() == "appsettings")
                {
                    XmlNodeList _node = element.ChildNodes;
                    if (_node.Count > 0)
                    {
                        foreach (XmlElement el in _node)
                        {
                            if (el.Attributes["key"].Value.ToLower() == key)
                            {
                                el.Attributes["value"].Value = value;
                                break;
                            }
                        }
                    }
                }
            }
            xmldoc.Save(filename);
        }






        int exist = 0;
        public int insertdatas(DataTable datatable)
        {

            HttpCookie acookie = new HttpCookie("comp");


            Maticsoft.BLL.CERTI bll = new Maticsoft.BLL.CERTI();
            int a = 0;

            string delete = "delete from CERTI where ZSH='{0}'";

            string select = "select count(0) from CERTI where ZSH='{0}'";

            int total = 0;

            if (datatable.Columns.Count == 7 || datatable.Columns.Count == 6)
            {
                foreach (DataRow datarow in datatable.Rows)
                {
                    string sql = "INSERT INTO CERTI(ZSH,JCS,PM,JJZ,BM,CREATEDATE,BZ)" + "VALUES('" + datarow[0].ToString().Trim() + "'" + ",'" + datarow[1].ToString() + "'" + ",'" + datarow[2].ToString() + "'" + ",'" + datarow[3].ToString() + "'" + ",'" + datarow[4].ToString() + "',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'" + datarow[5].ToString() + "')";

                    try
                    {
                        //a = (a + (DbHelperOra.ExecuteSql(sql,"")));
                        //DbHelperOra.ExecuteSql(sql, "");
                        if (RadioButton1.Checked)
                        {
                            bll.Add(string.Format(delete, datarow[0].ToString().Trim()), "");
                            a += bll.Add(sql, "");
                        }
                        else
                        {
                            int rest = Convert.ToInt32(bll.GetSingle(string.Format(select, datarow[0].ToString().Trim()), ""));

                            if (rest < 1)
                            {
                                a += bll.Add(sql, "");
                            }
                            else
                            {
                                exist++;
                            }

                        }

                    }
                    catch (Exception)
                    {

                        a--;
                    }

                    total++;
                    //acookie.Value = "正在导入，请稍后...("+datatable.Rows.Count+" / "+total+")";
                    //Button1_Click("com", "正在导入，请稍后...(" + datatable.Rows.Count + " / " + total + ")");
                    //acookie.Expires = DateTime.MaxValue;
                    //Response.Cookies.Add(acookie);


                }
            }
            else if (datatable.Columns.Count == 5)
            {
                foreach (DataRow datarow in datatable.Rows)
                {
                    string sql = "INSERT INTO CERTI(ZSH,JCS,PM,JJZ,BM,CREATEDATE)" + "VALUES('" + datarow[0].ToString().Trim() + "'" + ",'" + datarow[1].ToString() + "'" + ",'" + datarow[2].ToString() + "'" + ",'" + datarow[3].ToString() + "'" + ",'" + datarow[4].ToString() + "',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'))";

                    try
                    {
                        //a = (a + (DbHelperOra.ExecuteSql(sql,"")));
                        //DbHelperOra.ExecuteSql(sql, "");
                        if (RadioButton1.Checked)
                        {
                            bll.Add(string.Format(delete, datarow[0].ToString().Trim()), "");
                            a += bll.Add(sql, "");
                        }
                        else
                        {
                            int rest = Convert.ToInt32(bll.GetSingle(string.Format(select, datarow[0].ToString().Trim()), ""));

                            if (rest < 1)
                            {
                                a += bll.Add(sql, "");
                            }
                            else
                            {
                                exist++;
                            }

                        }

                    }
                    catch (Exception)
                    {

                        a--;
                    }

                    total++;
                    //acookie.Value = "正在导入，请稍后...("+datatable.Rows.Count+" / "+total+")";
                    //Button1_Click("com", "正在导入，请稍后...(" + datatable.Rows.Count + " / " + total + ")");
                    //acookie.Expires = DateTime.MaxValue;
                    //Response.Cookies.Add(acookie);


                }
            }
            else if (datatable.Columns.Count == 4)
            {

                foreach (DataRow datarow in datatable.Rows)
                {
                    string sql = "INSERT INTO CERTI(ZSH,JCS,PM,JJZ,CREATEDATE)" + "VALUES('" + datarow[0].ToString().Trim() + "'" + ",'" + datarow[1].ToString() + "'" + ",'" + datarow[2].ToString() + "'" + ",'" + datarow[3].ToString() + "',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'))";

                    try
                    {
                        //a = (a + (DbHelperOra.ExecuteSql(sql,"")));
                        //DbHelperOra.ExecuteSql(sql, "");
                        //bll.Add(string.Format(delete, datarow[0].ToString().Trim()), "");
                        if (RadioButton1.Checked)
                        {
                            bll.Add(string.Format(delete, datarow[0].ToString().Trim()), "");

                            a += bll.Add(sql, "");
                        }
                        else
                        {
                            int rest = Convert.ToInt32(bll.GetSingle(string.Format(select, datarow[0].ToString().Trim()), ""));
                            if (rest < 1)
                            {
                                a += bll.Add(sql, "");
                            }
                            else
                            {
                                exist++;
                            }
                        }

                    }
                    catch (Exception)
                    {

                        a--;
                    }


                    total++;

                    //acookie.Value = "正在导入，请稍后...(" + datatable.Rows.Count + " / " + total + ")";
                    //Button1_Click("com", "正在导入，请稍后...(" + datatable.Rows.Count + " / " + total + ")");
                    //acookie.Expires = DateTime.MaxValue;
                    //Response.Cookies.Add(acookie);
                }
            }


            Page.ClientScript.RegisterStartupScript(this.GetType(), "", "clearInterval(ids);", true);




            return a;
        }





        public void btnBrower_Click(object sender, EventArgs e)
        {

            if (FileUpload2.HasFile)
            {
                if (Path.GetExtension(FileUpload2.FileName).Trim().ToLower().Equals(".xls") || Path.GetExtension(FileUpload2.FileName).Trim().ToLower().Equals(".xlsx"))
                {

                    try
                    {
                        string filename = DateTime.Now.ToString("yyyyMMddHHmmssssfff") + Path.GetExtension(FileUpload2.FileName).Trim().ToLower();
                        string pat = Server.MapPath("~/Excel/");
                        FileUpload2.SaveAs(pat + filename);
                        //DataTable dt = GetExcelDataTable(FileUpload1.PostedFile.FileName, "Sheet1");
                        DataTable dt = GetExcelDataTable(pat + filename, "Sheet1");

                        dt = (ClearNullData(dt));

                        //ViewState["tablename"] = pat + filename;
                        ViewState["table"] = dt;
                        HiddenField2.Value = dt.Rows.Count.ToString();

                        BindDropList(DropDownList1);
                        BindDropList(DropDownList2);
                        BindDropList(DropDownListpm);

                        BindDropList(DropDownList3);
                        BindDropList(DropDownList4);
                        BindDropList(DropDownList5);
                        BindDropList(DropDownList6);
                        //DataView dv=dt.DefaultView;
                        //int res = insertdatas(dt);
                        //Label1.Text = "Excel中共包含有效数据 " + dt.Rows.Count + " 条,成功导入：" + res + " 条数据&nbsp;&nbsp;&nbsp;&nbsp;未导入：" + (dt.Rows.Count - res) + "条数据&nbsp;&nbsp;&nbsp;&nbsp;其中 " + exist + " 条数据已存在";
                        Label2.Text = "上传成功，请选择对应字段后点击导入按钮进行导入数据。";
                        Page.ClientScript.RegisterStartupScript(this.GetType(), "", "document.getElementById('ctl00_ContentPlaceHolder1_Button3').focus();", true);
                    }
                    catch (Exception ex)
                    {

                        Label2.Text = "上传失败，错误信息如下： <br /><span style='color:red'>" + ex.Message + "<br />" + ex.Source + "</span>";
                    }

                }
                else
                {
                    Maticsoft.Common.MessageBox.ShowAndRedirect(this, "请选择正确的 Excel 文件！", "Add.aspx");

                }

            }
            else
            {
                Maticsoft.Common.MessageBox.ShowAndRedirect(this, "请选择文件！", "Add.aspx");

            }
        }


        public void BindDropList(DropDownList lis)
        {
            if (returnCloulm() != null)
            {
                DataTable t = returnCloulm();

                DataRow dr = t.NewRow();
                dr[0] = " 无 ";
                t.Rows.InsertAt(dr, t.Rows.Count + 1);

                lis.DataSource = t;
                lis.DataTextField = "filds";
                lis.DataValueField = "filds";
                lis.DataBind();

                lis.SelectedIndex = t.Rows.Count-1;
                //lis.SelectedIndex =3;
            }


        }

        public void ClearBindDropList(DropDownList lis)
        {
            lis.Items.Clear();
            lis.DataSource = null;
            lis.DataBind();

        }


        public void btnSubmit_Click(object sender, EventArgs e)
        {
            //DataTable dt = ViewState["table"] as DataTable;

            if (returnCloulm() != null)
            {
                DataTable dt = ViewState["table"] as DataTable;

                int res = insertdatas2(dt);
                Label2.Text = "Excel中共包含有效数据 " + dt.Rows.Count + " 条,成功导入：" + res + " 条数据&nbsp;&nbsp;&nbsp;&nbsp;未导入：" + (dt.Rows.Count - res) + "条数据&nbsp;&nbsp;&nbsp;&nbsp;其中 " + exist + " 条数据已存在";

                //Page.ClientScript.RegisterStartupScript(this.GetType(), "", "document.getElementById('ctl00_ContentPlaceHolder1_Button3').focus();", true);

                ViewState["table"] = null;

                ClearBindDropList(DropDownList1);
                ClearBindDropList(DropDownList2);
                ClearBindDropList(DropDownListpm);

                ClearBindDropList(DropDownList3);
                ClearBindDropList(DropDownList4);
                ClearBindDropList(DropDownList5);
                ClearBindDropList(DropDownList6);

            }
            else
            {

                Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('请先上传要导入的Excel文件！');document.getElementById('ctl00_ContentPlaceHolder1_Button3').focus();", true);
            }


        }


        public int insertdatas2(DataTable datatable)
        {

            HttpCookie acookie = new HttpCookie("comp");


            Maticsoft.BLL.CERTI bll = new Maticsoft.BLL.CERTI();
            int a = 0;

            string delete = "delete from CERTI where ZSH='{0}'";

            string select = "select count(0) from CERTI where ZSH='{0}'";
            int total = 0;

            string sqls = "";

            string f = "";
            string b = "";

            //if(!DropDownList1.SelectedValue.Trim().Trim().Trim().Equals("无"))
            //{
            //    f = "ZSH,";
            //    b="'"+DropDownList1.SelectedValue.Trim().Trim().Trim()+"'";
            //}
            //if (!DropDownList2.SelectedValue.Trim().Trim().Trim().Equals("无"))
            //{
            //    f += "JCS,";
            //    b +="'"+DropDownList1.SelectedValue.Trim().Trim().Trim()+"'";
            //}
            //if (!DropDownListpm.SelectedValue.Trim().Trim().Trim().Equals("无"))
            //{
            //    f += "PM,";
            //    b +="'"+DropDownListpm.SelectedValue.Trim().Trim().Trim()+"'";
            //}

            //if (!DropDownList3.SelectedValue.Trim().Trim().Trim().Equals("无"))
            //{
            //    f += "JJZ,";
            //    b +="'"+DropDownList3.SelectedValue.Trim().Trim().Trim()+"'";
            //}
            //if (!DropDownList4.SelectedValue.Trim().Trim().Trim().Equals("无"))
            //{
            //    f += "BM,";
            //    b +="'"+DropDownList4.SelectedValue.Trim().Trim().Trim()+"'";
            //}
            //if (!DropDownList5.SelectedValue.Trim().Trim().Trim().Equals("无"))
            //{
            //    f += "BZ,";
            //    b +="'"+DropDownList5.SelectedValue.Trim().Trim().Trim()+"'";
            //}
            //if (!DropDownList6.SelectedValue.Trim().Trim().Trim().Equals("无"))
            //{
            //    f += "TEMP,";
            //    b +="'"+DropDownList6.SelectedValue.Trim().Trim().Trim()+"'";
            //}

            //f = f.TrimEnd(',');
            //b = b.TrimEnd(',');

            foreach (DataRow datarow in datatable.Rows)
            {
                //string sql = "INSERT INTO CERTI("+f+")" + "VALUES("+b+")";
                string sql = "INSERT INTO CERTI(ZSH,JCS,PM,JJZ,BM,CREATEDATE,BZ,TEMP)" + "VALUES('" + (DropDownList1.SelectedValue.IndexOf("无")>-1 ? "" : datarow[DropDownList1.SelectedValue].ToString()) + "','" + (DropDownList2.SelectedValue.IndexOf("无")>-1 ? "" : datarow[DropDownList2.SelectedValue].ToString()) + "','" + (DropDownListpm.SelectedValue.IndexOf("无")>-1 ? "" : datarow[DropDownListpm.SelectedValue].ToString()) + "','" + (DropDownList3.SelectedValue.IndexOf("无")>-1 ? "" : datarow[DropDownList3.SelectedValue].ToString()) + "','" + (DropDownList4.SelectedValue.IndexOf("无")>-1 ? "" : datarow[DropDownList4.SelectedValue].ToString()) + "',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'" + (DropDownList5.SelectedValue.IndexOf("无")>-1 ? "" : datarow[DropDownList5.SelectedValue].ToString()) + "','" + (DropDownList6.SelectedValue.IndexOf("无")>-1 ? "" : datarow[DropDownList6.SelectedValue].ToString()) + "')";

                //string sql = "INSERT INTO CERTI(ZSH,JCS,PM,JJZ,BM,CREATEDATE,BZ,TEMP)" + "VALUES('" + (DropDownList1.SelectedValue.Trim().Trim().Trim().Equals("无") ? "" : datarow[DropDownList1.SelectedValue.Trim().Trim().Trim()].ToString()) + "','" + (DropDownList1.SelectedValue.Trim().Trim().Trim().Equals("无") ? "" : datarow[DropDownList1.SelectedValue.Trim().Trim().Trim()].ToString()) + "')";
                try
                {
                    //a = (a + (DbHelperOra.ExecuteSql(sql,"")));
                    //DbHelperOra.ExecuteSql(sql, "");
                    if (RadioButton3.Checked)
                    {
                       int ext=bll.Add(string.Format(delete, datarow[(DropDownList1.SelectedValue)].ToString().Trim()), "");
                       if (ext>0)
                        {
                            exist++;
                            a--;
                        }
                        a += bll.Add(sql, "");
                    }
                    else
                    {
                        int rest = Convert.ToInt32(bll.GetSingle(string.Format(select, datarow[(DropDownList1.SelectedValue)].ToString().Trim()), ""));

                        if (rest < 1)
                        {
                            a += bll.Add(sql, "");
                        }
                        else
                        {
                            exist++;
                        }
                    }

                }
                catch (Exception)
                {

                    a--;
                }

                total++;
                //acookie.Value = "正在导入，请稍后...("+datatable.Rows.Count+" / "+total+")";
                //Button1_Click("com", "正在导入，请稍后...(" + datatable.Rows.Count + " / " + total + ")");
                //acookie.Expires = DateTime.MaxValue;
                //Response.Cookies.Add(acookie);
                //Response.Clear();
                //Response.Write("正在导入，请稍后...(" + datatable.Rows.Count + " / " + total + ")");
                //Response.End();
            }

            //Page.ClientScript.RegisterStartupScript(this.GetType(), "", "clearInterval(ids);", true);


            return a;
        }


        public DataTable returnCloulm()
        {
            DataTable t = new DataTable();

            DataColumn dc = null;
            dc = t.Columns.Add("filds", Type.GetType("System.String"));


            if (ViewState["table"] != null && (ViewState["table"] as DataTable) != null)
            {
                DataTable dt = ViewState["table"] as DataTable;
                for (int i = 0; i < dt.Columns.Count; i++)
                {

                    DataRow newRow;
                    newRow = t.NewRow();
                    newRow["filds"] = dt.Columns[i];
                    t.Rows.Add(newRow);

                }
            }
            else
            {
                return null;
            }

            return t;

        }

    }
}
